package tju.mic.medicine.ws.dao;


import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.support.DaoSupport;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Component;
import tju.mic.medicine.selfDefine.models.UserPrivilege;
import tju.mic.medicine.ws.models.SysMenu;
import tju.mic.medicine.ws.models.SysRole;
import tju.mic.medicine.ws.models.SysUser;

/**
 *
 * @author tjusnow
 */
@Component("systemDao")
public class SystemDao extends DaoSupport{
    
    
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public JdbcTemplate getJdbcTemplate() {
        return this.jdbcTemplate;
    }
    
    @Override
    protected void checkDaoConfig() throws IllegalArgumentException {
        if (this.jdbcTemplate == null) {
            throw new IllegalArgumentException(" 'mysqlJdbcTemplate' is required");
        }
    }
    
    public List<UserPrivilege> getGrantedMenuByUserId(Long userId){
        List<UserPrivilege> staffPrivList = new ArrayList<UserPrivilege>();
        String sql = "select E.id,E.icon,E.menu_name,E.menu_text,E.menu_rank,E.parent_id,A.staff_id,C.depart_scope,D.user_right "
                + "from medicine.user A,medicine.sys_role B,medicine.sys_user_role C,medicine.sys_role_menu D,medicine.sys_menu E "
                + "where A.user_id = C.user_id and B.id = C.ROLE_ID and B.id = D.role_id and D.menu_id = E.id "
                + "and C.user_id = ? order by E.id ";
        SqlRowSet rs = getJdbcTemplate().queryForRowSet(sql, userId);
        while(rs.next()){
            UserPrivilege priv = new UserPrivilege();
            priv.setUserId(userId);
            priv.setDepartScope(rs.getString("depart_scope"));
            priv.setUserRight(rs.getString("user_right"));
            SysMenu menu = new SysMenu();
            menu.setId(rs.getLong("id"));
            menu.setIcon(rs.getString("icon"));
            menu.setMenuName(rs.getString("menu_name"));
            menu.setMenuText(rs.getString("menu_text"));
            menu.setMenuRank(rs.getString("menu_rank"));
            menu.setParentId(rs.getString("parent_id"));
            priv.setMenu(menu);
            staffPrivList.add(priv);
        }
        return staffPrivList;
    }

    public SysUser checkUserPasswd(Long userId, String hashPassword) throws Exception{
        String sql = "select staff_id,name from medicine.sys_user "
                + "where user_id = ? and password = '"+hashPassword+"' ";
        SqlRowSet rs = getJdbcTemplate().queryForRowSet(sql, userId);
        
        if (rs.next()){
            SysUser user = new SysUser();
            user.setId(userId);
            user.setUserName(rs.getString("name"));
            return user;
        }
        return null;
    }

    public List<SysMenu> getGrantedMenuByDefaultRoles(List<SysRole> defaultRoles) throws Exception {
        List<SysMenu> list = new ArrayList<SysMenu>();
        String sql = "select E.id,E.icon,E.menu_name,E.menu_text,E.menu_rank,E.parent_id "
                + "from medicine.sys_role B,medicine.sys_role_menu D,medicine.sys_menu E "
                + "where B.id = D.role_id and D.menu_id = E.id ";
        String sqlWhere = " and ( B.id = "+defaultRoles.get(0).getId() +" ";
        for (int i=1;i<defaultRoles.size();i++){
            sqlWhere += " or B.id = " + defaultRoles.get(i).getId() + " ";
        }
        sqlWhere += " ) order by E.id";
        sql += sqlWhere;        
        SqlRowSet rs = getJdbcTemplate().queryForRowSet(sql);
        while(rs.next()){
            SysMenu menu = new SysMenu();
            menu.setId(rs.getLong("id"));
            menu.setIcon(rs.getString("icon"));
            menu.setMenuName(rs.getString("menu_name"));
            menu.setMenuText(rs.getString("menu_text"));
            menu.setMenuRank(rs.getString("menu_rank"));
            menu.setParentId(rs.getString("parent_id"));
            list.add(menu);
        }
        return list;
    }

    public boolean userHasPrivilege(Long userId, Integer menuId) {
        String sql = "select count(distinct(menu_id)) as cnt from ( "
                + "select D.MENU_ID "
                + "from medicine.sys_user A,medicine.sys_role B,medicine.sys_user_role C,medicine.sys_role_menu D "
                + "where A.user_id = C.user_id and B.id = C.ROLE_ID and B.id = D.role_id "
                + "and C.user_id = "+userId+" and D.menu_id =  "+menuId +" "
                + "UNION "
                + "select B.menu_id "
                + "from medicine.sys_role A,medicine.sys_role_menu B "
                + "where A.id = B.role_id and A.default_role = 1 and B.menu_id = "+menuId+") A ";
        SqlRowSet rs = getJdbcTemplate().queryForRowSet(sql);
        Integer cnt = null;
        if (rs.next()){
            cnt = rs.getInt("cnt");
        }
        return cnt>0;
    }

    public UserPrivilege getGrantedDepartScope(Long userId, Integer menuId) {
        String sql = "select A.menu_id,A.depart_scope,user_right from "
                + "(select D.MENU_ID,C.DEPART_SCOPE,D.USER_RIGHT "
                + "from medicine.sys_user A,medicine.sys_role B,medicine.sys_user_role C,medicine.sys_role_menu D "
                + "where A.user_id = C.user_id and B.id = C.ROLE_ID and B.id = D.role_id "
                + "and C.user_id = "+userId+" and D.menu_id =  "+menuId +" "
                + "UNION "
                + "select B.menu_id,'%',B.USER_RIGHT "
                + "from medicine.sys_role A,medicine.sys_role_menu B "
                + "where A.id = B.role_id and A.default_role = 1 and B.menu_id = "+menuId+") A ";
        SqlRowSet rs = getJdbcTemplate().queryForRowSet(sql);
        Integer userRight = 999;
        UserPrivilege priv = new UserPrivilege();
        while (rs.next()){
            if ("%".equals(rs.getString("depart_scope"))){
                priv.setDepartScope("%");
            }else if (priv.getDepartScope()==null){
                priv.setDepartScope(rs.getString("depart_scope"));
            }
            int ur = rs.getInt("user_right");
            if (ur < userRight){
                priv.setUserRight(rs.getString("user_right"));
                userRight = ur;
            }
        }
        return priv;
    }
    
}
